Addition and processing of continuous sql queries in a streaming relational database management system

ABSTRACT

Systems, methods, and media are disclosed herein that can be embodied in a traditional Relational Database Management System (RDBMS) in order to transform it into a Streaming Relational Database Management System (SRDBMS). An SRDBMS may provide functionality such as to manage and populate streams, tables, and archived stream histories and support the evaluation of continuous queries on streams and tables. Both continuous and snapshot queries support the full spectrum of the industry standard, widely used, Structured Query Language. The present technology can support a high number of concurrent continuous queries using a scalable and efficient shared query evaluation scheme, support on-the-fly addition of continuous queries into a mechanism that implements the shared evaluation scheme, reuse RDBMS modules such as relational operators and expression evaluators, and visualize results of continuous queries in real time

CROSS-REFERENCE TO RELATED APPLICATIONS

This application is a divisional application of U.S. patent applicationSer. No. 12/398,944, filed on Mar. 5, 2009, titled “Addition andProcessing of Continuous SQL Queries in a Streaming Relational DatabaseManagement System,” which claims the priority benefit of U.S.Provisional Patent Application No. 61/068,572, filed on Mar. 6, 2008,titled “On-the-Fly Addition, Shared Evaluation, and DeclarativeVisualization of Continuous SQL Queries in a Streaming RelationalDatabase Management System.” All of the above applications are herebyincorporated by reference in their entirety.

FIELD OF THE APPLICATION

The present application relates to database management.

SUMMARY

Various embodiments of the invention disclose techniques that can beembodied in a traditional RDBMS (Relational Database Management System)in order to transform it into an SRDBMS (Streaming Relational DatabaseManagement System). Such a transformed SRDBMS may provide at least thefollowing functionality:

-   -   1. Manage and populate streams, tables, and archived stream        histories.    -   2. Support the evaluation of continuous queries on streams and        tables.    -   3. Both continuous and snapshot queries support the full        spectrum of the industry standard, widely used, Structured Query        Language (SQL).

Various embodiments of the invention provide:

-   -   1. Support for a very high number of concurrent continuous        queries using a highly scalable and efficient shared query        evaluation scheme.    -   2. Support for on-the-fly addition of continuous queries into        the mechanism that implements the shared evaluation scheme.    -   3. To reuse existing modules of the RDBMS such as relational        operators and expression evaluators as much as possible.    -   4. To visualize the results of continuous queries in real time

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram illustrating a database centric environmentand a database stream environment.

FIG. 2 is a block diagram illustrating an embodiment of a streamingrelational database management system.

FIG. 3 is a block diagram illustrating an embodiment of a streamingrelational database management system.

FIG. 4 illustrates a prompt for entering query parameters in a streamingrelational database management system.

DETAILED DESCRIPTION

A Relational Database Management System (RDBMS) is used to store andmanipulate finite sets of structured data. At a bare minimum, an RDBMSprovides facilities to create and populate database objects such astables, modify the contents of these objects, and evaluate SQL queriesthat process one or more tables in order to produce a relation as anoutput. Moreover, a traditional database uses a paradigm called “storefirst, query later” where new data is stored in the database before itcan be queried. In effect, these systems manage data that is “at rest”where a query operates on a snapshot of the database at any point intime—such a query is called “snapshot queries” (SQ). An SQ runs in afinite amount of time and produces a single set of records every time itis invoked.

FIG. 1 illustrates the contrasts between database-centric approachesversus datastream-centric approaches. Streams of data may include datathat is “on the move” in addition to data that is at rest. Systems thatmanage streams of data are called Streaming Relational DatabaseManagement Systems (SRDBMS) and are disclosed herein. A query that isdeployed over one or more stream in an SRDBMS runs forever and is calleda “continuous query” (CQ). As a new datum enters the system it isprocessed in order to produce additional results for the queries.Logically, a stream of data can be thought of as an unbounded set oftuples (i.e., records), ordered by a designated timestamp attributecalled the CQTIME attribute. A stream appears in a CQ with an associated“stream-to-relation” (StoR) or window clause that expresses how togenerate an ordered sequence of finite relations from an unboundedstream. The semantics of a CQ is to apply its associated SQ (formed byeliding all StoR clauses in the CQ) in turn on each such finite relationin the generated sequence, and concatenating the resulting relationsinto an output stream.

A stream query processor such as an SRDBMS is primarily used formonitoring and alerting applications. However, an SRDBMS is more than avanilla stream query processor since it integrates the world of streamswith those of relations. Thus, in addition to monitoring applications,an SRDBMS can be used to dramatically speed up the performance oftraditional analytical and reporting database systems. This performanceboost is achieved by exploiting the fact that all data originates aspart of some stream (e.g., application, transaction, logs) and can bepre-processed in incremental fashion using CQ technology.

Various embodiments of the invention can be realized using severaltechniques that are added to a traditional RDBMS along with thedevelopment of a client-server internet infrastructure. We firstdescribe the general architecture of such a system (a Streaming RDBMS)and then embodiments of the invention. Note that, while this documentrefers to the names of certain data structures and components from thePostgreSQL RDBMS for ease of exposition, the techniques that comprisethe embodiments of the invention can be implemented in any traditionalRDBMS.

FIG. 2 illustrates an exemplary environment in which embodiments of thepresent invention may be practiced. FIG. 2 illustrates a block diagramof an embodiment of a streaming relational database management system(SRDBMS) 200 in accordance with aspects of the technology. As with allother figures provided herein, FIG. 2 is exemplary only. The system 200includes a client 202 in communication with a server 210. For simplicityand clarity, only one client 202 is illustrated in FIG. 2. However, aperson having ordinary skill in the art will appreciate that multipleclients 202 may communicate with the server 210.

In some embodiments, the server 210 may include a bulk loader 212, anegress backend 214, an optional archiver 216, a disk 218, data/controlqueues 220, a buffer pool 222, and a runtime module 230. The egressbackend 214 may include a planner, a parser, an optimizer, an executor,and/or the like (not illustrated). The buffer pool 222 may be a diskcache. The runtime module 230 includes a plan folding module 232, atuple router 234, and a data structure module 236. Further details ofthese elements are provided later herein.

The bulk loader 212 is configured to receive tuples from one or morestream 204A . . . 204 n. In some embodiments, the bulk loader 212receives tuples from a client application (not shown) configured toprocess streams of data (e.g., stream 204A-204 n) and provide the tuplesto the bulk loader 212. For simplicity and clarity, only one bulk loader212 is illustrated in FIG. 2. However, a person having ordinary skill inthe art will appreciate that the server 210 may include multiple bulkloaders 212 in communication with multiple streams 204A . . . 204 n. Insome embodiments, the streams 204A . . . 204 n communicate the tuples tothe server 210 via a network (not shown). The network may be a localarea network, a wide area network, a wireless network, a mobile network,the Internet, the world wide web, a client and/or the like. Vast arraysof information may be accessible via data sources coupled to networksother than Streams 204A . . . 204 n. For example, data can be suppliedby one or more data sources within a local area network, a wide areanetwork, and/or a mobile network. The bulk loader 212 may provide thetuples received from the streams 204A . . . 204 n to the data/controlqueues 220.

In some embodiments, the bulk loader 212 and the egress backend 214comprise common code configured to receive data including queries and/ortuples from a client application. The common code may be referred to asa bulk loader 212 when receiving tuples. The common code may be referredto as an egress backend 214 when receiving a query.

The data/control queues 220 may be configured to receive tuples from thebulk loader 212 and/or the runtime module 230. The data/control queues220 may provide the received tuples to the archiver 216, the runtimemodule 230, and/or the egress backend 214. For example, data/controlqueues 220 may provide the tuples received from the bulk loader 212and/or the runtime module 230 to the archiver 216 for storage on thedisk 218. The data/control queues 220 may also retrieve the tuples fromthe bulk loader 212 and provide them to the runtime module 230 forprocessing.

In some embodiments, the data/control queues 220 and the buffer pool 222both occupy shared memory. However, they may serve different functions.For example, the buffer pool 222 may function as a disk cache configuredto store and retrieve data from tables (e.g., on the disk 218). Thus,the archiver 216 may write data to the disk 218 via the buffer pool 222.However, the buffer pool 222 may be separate and distinct from thedata/control queues 220.

The disk 218 may be a computer readable medium configured to store data.Computer readable storage media may include a hard disk, random accessmemory, read only memory, an optical disk, a magnetic disk, virtualmemory, a network disk, and/or the like. Data may include tuples,tables, variables, constants, queries, continuous queries, programs,IMPs, SCP s, and/or the like. The disk 218 may also store instructionsfor execution by a processor (not shown), which causes the processor tomanage a query. In various embodiments, instructions for execution onthe processor include instructions for implementing the bulk loader 212,the egress backend 214, the archiver 216, the data/control queues 220,the buffer pool 222, and/or the runtime module 230 (including the planfolding module 232, the tuple router 234, and the data structure module236).

The egress backend 214 may receive a query from the client 202. Invarious embodiments, the query is a CQ or a static query. Optionally,the query received from the client 202 is a new query. The egressbackend 214 is configured to process the new query using a planner, aparser, an optimizer and/or an executor. In some embodiments, the egressbackend 214 provides an IMP to the data/control queues 220. Thedata/control queues 220 may provide the IMP to the runtime module 230.The plan folding module 232 of runtime module 230 is configured to foldthe received IMP into a SCP in the data structure module 236.Alternatively, the data/control queues 220 may store the IMP on the disk218 (e.g., via the archiver 216 and the buffer pool 222) for later use.

In some embodiments, the data/control queues 220 receives databaseobjects (such as streams, tables, relations, views, and/or the like) andprovides the database objects to the buffer pool 222 via the archiver216. The buffer pool 222 may store the database objects on the disk 218.The data/control queues 220 may provide the database objects to theruntime module 230. Optionally, the data/control queues 220 providemultiple objects to the runtime module 230 at the same time. Forexample, the data/control queues 220 may provide tuples from two or morestreams 204A . . . 204 n and a table to a SCP in the data structuremodule 236 of the runtime module 230.

The runtime module 230 is configured to receive tuples and/or tablesfrom the data/control queues 220. The runtime module 230 may evaluatethe tuples and/or tables using the tuple router 234 and the datastructures in the data structure module 236. In various embodiments, theruntime module 230 outputs streams, tables, and/or data to thedata/control queues 220. The data/control queues 220 may communicateoutput from the runtime module 230 to the client 202 via the egressbackend 214. For example, the data/control queues 220 may receive atuple from the runtime module 230 and provide the tuple to the archiver216. The archiver 216 may provide the tuple to the buffer pool 222. Thebuffer pool 222 may provide the tuple to the egress backend 214. Theegress backend 214 may provide the tuple to the client 202.Alternatively, the buffer pool 222 may provide the tuple to the disk218. Thus, the data/control queues 220 may store output from the runtimemodule 230 onto the disk 218 via the archiver 216 and the buffer pool222.

Various kinds of objects (e.g., tables or views) behave like relationsin a traditional RDBMS. Objects that behave like streams may be used inthe SRDBMS 200. In various embodiments, a stream (e.g., the streams 204A. . . 204 n) is classified as a raw stream or derived stream. Theclassification may be based on how the stream is populated. A raw streammay be populated by an external data provider. In some embodiments, theexternal data provider connects to the server 210 using a secure andwell-defined protocol that authenticates itself, and advertises theidentity of the stream being populated. If authorized, the provider mayprovide data tuples that are appended to the stream. In practice, a dataprovider will use a call-level API that the SRDBMS provides.

A produced stream may be defined using a query (e.g., a defining query)and may be populated by the SRDBMS 200. A produced stream may be one ofmultiple types of database objects, including a view and a derivedstream. A view may be a database object that is defined using a queryand has macro semantics. A view may be defined using a CQ as a streamingview. A view may be used in another query in a place that a raw streamcan be used. A defining query of a view runs only when a query that usesthe view runs as well.

In some embodiments, a derived stream is a materialized CQ that may beassociated with a stream using a special syntax (e.g., CREATE STREAM . .. AS query). The associated stream is similar to a view and may be usedin another query in a place that a raw stream can be used. Unlike aview, however, a derived stream does not have macro semantics. A derivedstream may be active, whether or not it is used in another active query.A raw stream and/or a derived stream may be stored on the disk 218, asdiscussed elsewhere herein. For example, a raw stream and/or a derivedstream may be archived using the archiver 216 for storing on the disk218.

In some embodiments, the runtime module 230 receives a query from thedisk 218 via the buffer pool 222. The runtime module 230 may alsoreceive various kinds of objects (e.g., a stream, a data table,metadata, tuples, views, relations, expressions, and/or the like) fromthe buffer pool 222. These objects may be received by the buffer pool222 from the disk 218 and/or the archiver 216.

FIG. 3 illustrates the architecture of an SRDBMS that is built usingsuch an RDBMS as a substrate. The following embodiments are exemplaryand are meant to be illustrative of the disclosure herein. The examplesare not intended to be interpreted as limiting the scope of the presentdisclosure.

In a traditional process-oriented RDBMS, there is generally a processthat listens on to incoming connections on a specified socket (theListener) and forks off a separate “backend process” to handle a newconnection. In addition to the Listener, there may be a processdedicated to shared evaluation of CQs (the Runtime) as well as processdedicated to archiving the results of CQs (the Archiver) in tables. TheArchiver process is used to materialize the results of a CQ into apersistent RDBMS object such as a traditional table, called an activetable. Ingress and Egress of data may be accomplished using standardbackend processes and protocols. More specifically, a client producingdata for a stream can connect to a backend and use the standard bulkloader for tables (a protocol called COPY) to push data into astream—the backend process takes the incoming records and writes themonto data queues. A client that needs to consume data from the systemcan connect to a backend and issue a CQ using a cursor (in a manneridentical to an SQ) and continuously fetch the results of the CQ bymanipulating the cursor.

When a backend process receives a query it uses an optimizer to producean execution plan that comprises a tree of relational operatorsconforming to the well-known “iterator” model. If the query in questionis an SQ, the backend evaluates the execution plan individually usingits executor component, as would the case be in a traditional RDBMS. If,on the other hand, the query is a CQ the backend sends the associatedexecution plan to the Runtime for shared evaluation using a controlqueue. In the latter case, for example, the backend actually evaluates asmall “stub” execution plan that consists solely of a Scan operator thatreads records from an internal queue in response to FETCH requests froma cursor.

When the Runtime process fetches a new query plan, it merges the newquery plan on-the-fly onto a novel shared query plan—this process iscalled “plan folding.” The shared query plan comprises a set of specialCQ operators and an associated “routing table.” Apart from plan folding,a responsibility of the Runtime is to process incoming data records thatare fetched from data queues. The runtime accomplishes this byadaptively routing tuples through the CQ operators that constitute theshared plan. These CQ operators perform their associated tasks byreusing the existing implementation of iterator-style relationaloperators in the RDBMS. In other words, these CQ operators maintain theinformation associated with sharing multiple concurrent queries by“orchestrating” an arbitrary portion of a standard query plan. Some ofthese CQ operators, have the added responsibility of writing theirresults to data queues, from where the tuples are further processed byeither an egress backend or the Archiver process.

A stream can be thought of as an unbounded, potentially infinite, bag oftuples, where each tuple has a clearly delineated “timestamp” attribute.In practice, a stream is a database object that is defined with a schemasimilar to that of a relation, and tuples are appended to a stream asthey “arrive” in the system.

The schema of a stream is distinguished from that of a relation by thefollowing requirements:

-   -   1. The designated “timestamp” attribute of a stream is        identified using a CQTIME constraint with syntax that is similar        to that of NOT NULL and UNIQUE constraints while defining        tables.    -   2. Every stream has one, and only one, attribute with a CQTIME        constraint, and this attribute has an ordinal type that is one        of SMALLINT, INTEGER, BIGINT, TIMESTAMP, or TIMESTAMPTZ.    -   3. The values of the CQTIME attribute are monotonically        increasing for newer tuples that arrive in the stream.

In the SRDBMS, there can be different kinds of database objects thatbehave like streams, just as there can be different kinds of objectsthat behave like relations (e.g., tables, views) in a traditional RDBMS.Thus, streams can be classified as being, for example, raw streams, orderived streams, based on how they are populated:

-   1. Raw streams. A raw stream is populated by an external data    provider that connects to the system using a secure and well-defined    protocol that authenticates itself, and advertises the identity of    the stream being populated. If authorized, the provider then    proceeds to pump in data tuples that are appended to the stream. In    practice, a data provider will use a call-level API that the SRDBMS    provides.-   2. Produced streams. A produced stream is sometimes defined using a    query (the “defining query”) and is populated by the SRDBMS. A    produced stream can be one of the following types of database    objects:    -   a. View. A view that is defined with a continuous query is a        streaming view, and can be used in another query in any place        that a raw stream can be used. The defining query of a view runs        when a query that uses the view is actually running. This is        because a view is a database object that is defined with a query        and has “macro” semantics. A use of a view in another query is        identical to explicitly exploding the view's defining query as a        sub-query.    -   b. Derived streams. A materialized continuous query is        explicitly associated with a stream using a special syntax:        CREATE STREAM . . . AS query. The associated stream is similar        to a view and may be used in another query in any place that a        raw stream can be used. Unlike a view, however, a materialized        query does not have macro semantics, and is sometimes active        whether or not it is used in another active query.

A raw or derived stream can optionally be archived.

In order to provide context for the details of the embodiments of theinvention disclosed herein, a brief description of the syntax andsemantics of SQL-based continuous queries is provided. A continuousquery (CQ) operates over a set of streams and relations, and produces astream as output. In order to understand the execution model of a CQ aCQ is distinguished from a snapshot query (SQ) in at least the followingways:

-   -   1. The FROM clause of a CQ has at least one stream.    -   2. Streams may not appear anywhere else (e.g. WHERE clause) in a        CQ.    -   3. A stream in the FROM clause of a CQ may be a stream, a        Derived streams, a view, or an inline sub-query. Furthermore, a        streaming sub-query may be executed as an independent “inner” CQ        that produces streaming results that can be used to process the        “outer” CQ.    -   4. A stream in the FROM clause of a CQ can be optionally        associated with a Stream-to-Relation (StoR) operator (informally        called a “window” clause). The StoR operator describes both the        content of a visible set, as well as how the visible set changes        over time (e.g., advance by row, advance by time). A visible set        of a stream can be thought of as a temporary relation and is        valid until it is redefined by the next visible set produced for        the same stream.    -   5. While there can be more than one stream in the same FROM        clause of a given CQ, in order to express a stream-stream join,        or a self-join involving streams, all but one of these streams        may be defined with a special StoR called a “current window.”        The “current window” of a stream treats the latest window        produced by it as a finite set of records.    -   6. The SELECT clause of a CQ can optionally be associated with a        Relation-to-Stream (RtoS) operator that is defined using syntax        similar to a DISTINCT clause. An RtoS operator takes a sequence        of relations as input and produces a sequence of relations as        output.    -   7. The Relation-to-Relation (RtoR) operator corresponds to the        underlying SQ that can be produced by stripping the CQ of its        StoR and RtoS clauses.

The SRDBMS offers the capability to execute continuous queries overstreams and relations in the standard, and well-understood, SQL querylanguage.

The execution of a CQ can be easily and unambiguously understood interms of its StoR operators, its RtoS operator, and its RtoR operator asfollows:

-   -   1. Apply StoR operators: Every StoR operator (informally called        a “window”) is continually applied to each associated stream in        order to produce an unbounded sequence of visible sets.    -   2. Apply RtoR operator: Every time a new visible set is produced        by an StoR operator, the RtoR operator (i.e., the underlying SQ)        is applied on the visible sets and other relations in the FROM        clause in order to produce an unbounded sequence of relations.    -   3. Relation-to-Stream (RtoS) operator: Every time a new relation        is produced by applying the RtoR operator, it is used to produce        a new “window” of tuples, generally by comparing the relation        freshly produced by the RtoR operator with previous relations        produced by the RtoR operator. The RtoS operator in the SRDBMS        may be used to append successive windows of tuples in order to        form a stream.

Stream-to-Relation (StoR) Operators

The SRDBMS offers a wide range of StoR operators in order to satisfy avariety of use cases. Most of these variants are specified in terms of“intervals” that describe contiguous subsequences of the underlyingstream. These intervals can be specified in three different kinds ofunits:

-   -   1. Row-based: This interval contains a fixed number of rows in        the associated stream. The interval is sometimes specified as an        integer: for example, 25 ROWS.    -   2. Time-based: This interval contains all the rows that fall        into a fixed range of time in the associated stream. The data        type of this interval depends on the CQTIME type used by the        associated stream. For example, when the CQTIME of the stream is        a TIMESTAMP, a time-based interval is specified as an INTERVAL        value.    -   3. Window-based: This interval can only be used with a produced        stream. It contains all the rows in a fixed number of windows in        the underlying stream. It therefore provides a level of        abstraction, allowing the properties of a higher-level query to        be specified in terms of the StoR used by a lower-level query.

More specifically, the SRDBMS supports the following varieties of StoRoperators (the details and the formal syntax are explained herein):

-   -   1. Sliding windows: A sliding window is expressed using an        advance interval, and a visible interval. The former defines the        periodic intervals (and thus, the actual edges) at which a new        visible set is constructed from the stream, while the latter        defines the interval of tuples, relative to the periodic edges,        that belong in each visible set. Note that both intervals can be        either time or row based intervals. When the visible interval        exceeds the advance interval, successive visible sets can be        thought of as being “sliding” or “moving” windows, and a tuple        in a stream can thus belong to multiple visible sets.    -   2. Chunking windows: A chunking window is expressed using either        a SAME TIME clause, or a sequence of intervals of the same type        (e.g., ‘2 seconds’, ‘3 seconds’, ‘2 seconds’). In the former        case, a new visible set is defined every time there is a new        tuple in the stream with a timestamp (CQTIME attribute) that is        different from the previous tuple. That is, each visible set        comprises all tuples with identical timestamps. In the latter        case, visible sets correspond to sets of tuples whose sizes are        defined by the sequence of intervals that is used to express the        window, and these visible sets continuously cycle through the        sequence of intervals with a period equal to the sum of the        intervals in the sequence. Note that in both cases, the        underlying stream is broken into successive, contiguous, and        non-overlapping “chunks” of tuples.¹ ¹ Such non-overlapping        windows are often efficient to implement as they generally        require no buffering of input data.    -   3. Landmark windows: A landmark window is expressed using an        advance interval, and a reset interval. The former defines the        periodic interval (and thus, the actual “advance edges”) at        which a new visible set is constructed from the stream, while        the latter defines a periodic interval that is used to compute a        sequence of “reset” edges. Each visible set comprises all tuples        that have arrived in the stream after the latest reset edge. The        landmark window is unbounded, and can be visualized as a        “rubber-band interval” with a fixed left edge, and a right edge        that keeps stretching at every advance point, and a left edge        that catches up (snaps) with the right edge at every reset        point. Note that both the advance and reset intervals can be        either time or row based.        The SELECT command

The enhancements (shown in bold) made to the SELECT statement of ISOstandard SQL in order to support streams are described in the followingsyntax diagrams:

SELECT     [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]    * |expression [ AS output_name ] [, ...]    [ FROM from_item [stream_to_relation ] [, ...] ]    [ WHERE condition ]    [ GROUP BYexpression [, ...] ]    [ HAVING condition [, ...] ]    [ { UNION |INTERSECT | EXCEPT } [ ALL ] select ]    [ ORDER BY expression [ ASC |DESC | USING operator ]      [, ...] ]    [ LIMIT { count | ALL } ]    [OFFSET start ]and stream_torel is:

<window_expr [START AT ‘time’]>

where window_expr can be one of:

VISIBLE slice_expr ADVANCE slice_expr

[SAME TIME]|SLICES slice_expr [, . . . ]

LANDMARK RESET AFTER slice_expr ADVANCE slice_expr

where slice_expr can be one of:

‘interval’

integer-const ROWS

integer-const WINDOWS

The clauses that already exist in SQL are available in the ISO standard.We now explain the new parameters that are specific to the CQE, andwhose syntax was described above:

-   -   1. stream_to_relation: This optional clause is used to express        the StoR operator for a stream in a CQ that was explained        earlier.        -   The StoR is only valid if the from_item is a stream, a            streaming view, a derived stream, or an inline streaming            sub-select (i.e., a sub-select that is a CQ). Note that it            is perfectly legal to have a non-streaming sub-select that            operates only over relations.        -   If an element of the FROM clause specifies a stream but no            StoR is specified, a default StoR clause is used. If the            associated stream is a raw stream, the default StoR is            equivalent to <slices 1 rows>; if the associated stream is a            streaming sub-query or a derived stream, the default StoR is            <slices 1 windows>.

Summary of Techniques

We now disclose a summary of techniques in various embodiments of theinvention. Details on each of these techniques will be provided insubsequent sections of this document.

-   -   1. The Shared CO Executor and Plan Folding: A principled        algorithm that walks the operators of a classical iterator-style        query plan in order to produce a “recipe of instructions” that        govern how the query plan can be folded into the shared CQ plan.    -   2. Orchestrating iterator-model sub-plans for streaming: A        mechanism for shared evaluation of relational algebra on        streaming data that orchestrates data through an arbitrary        sub-plan of iterator-model operators.    -   3. Unified windowing infrastructure: This is a method to        implement different kinds of StoR clauses (sliding, chunking,        landmark) in a unified fashion to support shared evaluation. A        signal innovation is the data-driven approach to additive lag        elimination—a critical feature to reduce latency in query        results.    -   4. Visualization infrastructure: This is a mechanism that        enables users to configure rich and complex dashboards that are        driven by the results of continuous queries.

The Shared CO Executor and Plan Folding

In this section, we describe two aspects of an SRDBMS:

-   -   1. How it represents and executes multiple concurrent CQs in a        shared fashion    -   2. How it “folds” a classic query plan for a new CQ on to an        existing shared plan used to process multiple concurrent CQs.

As a prelude to the description of plan folding, we first present abrief overview of query evaluation plans that use the iterator model andhow such a plan is processed by a traditional executor.²

Iterator model Ouery Plans and Executor

A query plan that conforms to the iterator-model is typicallyrepresented as a tree of operators. The leaves of the tree are scanoperators that fetch records from data sources, typically heap and indexfiles located on attached storage. Most operators are either unary(e.g., Aggregate) or binary (e.g., Joins), although n-ary operators arepossible (e.g., Union). A query plan is produced by an optimizer,typically after parse and rewrites phases, and then evaluated using anexecutor. An example iterator model plan for a simple query is shown inthe simple iterator model below.

The executor processes a tree of operators that are each represented bya “plan nodes.” The plan tree is a demand-pull pipeline of tupleprocessing operations. Each node, when called, will produce the nexttuple in its output sequence, or NULL if no more tuples are available.If the node is not a primitive relation-scanning node, it will havechild node(s) that it calls in turn to obtain input tuples.

EXPLAIN Produced for a Simple Iterator Model Query Plan

cqdb=# explain select a, sum(b) from foo where c > 10 group by a orderby a limit 10; QUERY PLAN----------------------------------------------------------------------- Limit (cost=37.94..37.97 rows=10 width=8) −> Sort (cost=37.94..38.11rows=67 width=8) Sort Key: a −> HashAggregate (cost=35.08..35.91 rows=67width=8) −> Seq Scan on foo (cost=0.00..32.12 rows=590 width=8) Filter:(c > 10) (6 rows)

Refinements on this model include:

-   -   1. Rescan command to reset a node and make it generate its        output sequence over again.    -   2. Parameters that can alter a node's results. After adjusting a        parameter, the rescan command is applied to that node and all        nodes above it.

More precisely, an operator is typically implemented in a genericfashion totally independent of its children. Thus, any function invokedon an operator in the tree, results in the same call being invoked onits children. Each operator implements the “iterator” interface thatconsists primarily of the following four functions:

-   -   init( ): Initializes the operator    -   next( ): Fetches the next tuple, typically by repeatedly calling        next( ) on its children until a tuple can be produced.    -   rescan( ): Resets the operator with any associated parameters        whose values are bound on-the-fly such as index keys. In        practice, rescan( ) is actually called by the dispatcher routine        for next( ) that consults a lookup table to figure out which        specific function to call for a given operator. The dispatcher        checks to see if any parameter associated with the operator has        been reset and if so it calls rescan( ) on the operator before        calling next( )    -   close( ): Shuts down the operator

A sub-component of the executor is the “expression evaluator.” This isresponsible for evaluating various expressions (for target listprojection, qualification conditions etc.) that are based on data fromtuples that are fetched from the operator's children. An expression isgenerally represented as a straightforward parse-tree where the atomicnodes are either constants or attributes (called Var nodes) from a tupleproduced by a child of the operator. While each Var node generally onlyneeds to identify the specific child that produced the tuple as well asthe position of the attribute within the tuple, it is generally possibleto further decorate the Var node to identify the specific original Scannode and attribute position it is derived from, provided the attributeis not entirely produced by an intermediate operator, such as anAggregate, in the query plan. The original Scan is identified in the Varnode with a reference to an entry in a data structure called “rangetable” which has one entry for each data source in a singleselect-project-join (SPJ) query block. Note that a nested sub-selectthat appears in a FROM clause will also result in an entry in the rangetable.

Shared CO Plan and Tuple Router

The shared CQ plan (SCP) is a data structure that includes specializedCQ operators. The SCP is processed by a novel executor called theTupleRouter in a manner explained more fully herein. An SCP is similarto an iterator model plan (IMP) only in as much as both data structuresrepresent dataflow operations. However, an SCP differs from an IMP inmany ways and has at least the following properties:

-   -   An SCP can be thought of as a directed acyclic graph (DAG) and        not a tree of operators.    -   An SCP is used to produce and process CQ tuples that consist of        a combination of individual “base” tuples and metadata        associated with book-keeping for sharing. The signature of a CQ        tuple is a composite key with one entry for each constituent        base tuple where each entry uniquely identifies the operator        that produced the associated base tuple.    -   An SCP operates by “pushing” CQ tuples from leaf (upstream)        nodes to higher level (downstream) nodes.    -   An SCP is flexible and capable of accommodating        changes—typically in the form of query addition and removal.    -   An SCP allows for adaptive tuple routing and can accommodate        multiple different routes for data. In other words, multiple CQ        tuples that are produced by the same operator (i.e., have the        same signature) can take different paths through subsequent        operators.

In some embodiments, an SCP may be represented by a routing table thatencodes the various possible routes for tuples in a shared dataflow.This routing table is implemented as a hash table that maps CQ tuplesignatures to an OpGroup, i.e., a group of CQ operators that areintended recipients of the CQ tuple.

Similarly, although a CQ operator has a superficial similarity to atraditional IMP operator it actually implements an interface with thefollowing substantial differences:

-   -   Since the SCP is “push” based, a CQ operator is called with an        input CQ tuple to process. This is different from an IMP        operator that is called with no inputs but relies on calling its        children for tuples to process.    -   As part of processing its input CQ tuple, the operator may        produce a set of output tuples. Each of these output tuples have        to be sent to one or more downstream operators. This is        accomplished by having the CQ operator pass each output tuple to        the TupleRouter for further downstream evaluation.    -   Since a CQ operator may be shared amongst different queries it        needs to support ways to add and remove queries on the fly.

In some embodiments, a CQ operator implements the following interfaces:

init( ): Initialize the operator

exec(CqTuple *): Execute the operator

add_query( ): Add a query to the operator

remove_query( ): Remove a query from the operator

end( ): Destroy the operator

As its name suggests, the TupleRouter is responsible for “routing” CQtuples through a network of CQ operators. Unlike a traditional IMPexecutor which processes tuples through a static fixed dataflow, theTupleRouter processes tuples through an adaptive dataflow wheresuccessive tuples with identical signatures can take different pathsthrough the network of CQ operators. Thus, given a CQ tuple, a centralfunction of the TupleRouter is to lookup the OpGroup corresponding toits signature, and route the tuple to each of the candidate operators inthe OpGroup. Furthermore, the operators in an OpGroup are subdividedinto a set of ordered subgroups based on certain rules of precedence. Aspart of the routing process the TupleRouter conforms to these precedencerules by ensuring that a CQ tuple is routed to all operators of a givensubgroup before being routed to those of a subgroup with lower priority.The TupleRouter is, however, free to route a CQ tuple to the operatorsof a given subgroup in any order or based on any policy. A particularlyeffective policy is a lottery scheduling based approach which favorsmore efficient operators (i.e., those that eliminate a CQ tuple early ina dataflow).

In practice, the TupleRouter operates in a single thread of controlorchestrated in a “major loop.” In each iteration of the major loop, therouter picks a leaf node (typically a SharedScan operator) and executesit by calling its exec( ) method. The leaf node produces an appropriateCQ tuple by reading off an input queue and then calls on the TupleRouterto further route the tuple to other downstream operators in the dataflownetwork.

The Plan Folding Algorithm

Plan Folding adds a new CQ onto a shared CQ plan, an data structure thatis used to represent multiple concurrent CQs that are being executed inthe Runtime process. The input for the plan folding algorithm is aniterator-model query plan (IMP) of the sort described above that isformed by running a standard query optimizer on a CQ, and where scans onstreams are modeled with a StoRScan above a StreamScan node.

The CQ Runtime process is responsible for plan folding. When a new queryis added to the system, the Runtime traverses the resulting IMP-styleplan bottom-up and folds it into the tuple router—that is—it createsshared plan items, if necessary, which can be shared among queries.

Recall that an iterator-model plan has an accompanying local “rangetable” that has information identifying all relations and streams thatare referenced in the plan. A range table is, in essence, a list of“range variables” each of which uniquely identifies atable/stream/function/sub-query. Similarly, the shared CQ plan, has anaccompanying data structure called a “global range table” thatidentifies all relations and streams from various different queries thatit references.

The components of plan folding may include the “varno” and “attno”transformations. The variables in the targetlist, quals etc. for anyplan are represented by a Var structure, and are characterized by 2 maincomponents (among others), namely the varno and attno. The varnoidentifies a range variable the from range table that in turn describesa specific table/stream that the variable is from, and the attnorepresents the attribute number of this variable in that relation. Whilethe varno and attno may be local to a single specific query in the IMP,they are transformed in the shared CQ plan to ensure there are noconflicts and collisions across all the queries that the planrepresents. Furthermore, these new varnos will be indexes into the CQTuple to get to the constituent “base” tuple. The transformed attnoswill, in turn, identify a specific attribute in the “base tuple.” Thevarno and attno transforms only change the varnoold and varoattno fieldsof the Var structure that are only used for debugging purposes in theRDBMS. The SRDBMS exploits these fields for execution. The fields thatare used for execution in the RDBMS are the varno and varattno.

In summary, the plan folding process is accomplished by at least thefollowing steps:

-   -   1. Non-destructively walk the new query plan bottom-up        generating “transforms”, “plan items” and “qual items”        -   The transforms are applied to internal structures like            varnos, attnos, scanrelids etc. so that the new plan can            reference the global range variables rather than those            present in the query's local range table.        -   The plan items represent stubs of information that are used            to determine either what is added to an already existing CQ            operator, or a new CQ operator that is added to the plan.        -   The qual items are similar to plan items and represent            information specific to qualifiers, or predicates.    -   2. Apply the transforms—note that the order of the following        operations is relevant:        -   a. Apply the attno transforms to the input plan.        -   b. Apply the varno transforms to the input plan    -   3. Modify the shared CQ plan appropriately:        -   a. Apply the qual items.        -   b. Apply the plan items.

Plan folding rules for various types of shared plan items, along withsome illustrative examples, are described below.

Shared Scans

-   -   1. Create a shared scan plan item (if it does not exist already)    -   2. Create a varno transform from the scanrelid to the plan        item's varno.    -   3. If it is not a streamscan, then this scan is on the right        side of a mixed join. We cap this transform because this        transform was only to enable the correct seek scan. All the        ancestors of this mixed join refer to it as coming from a        subplan item of the mixed join (see folding mixed join for        details).    -   4. No attno transformations are required because this is the        leaf node and the data is coming from only one source so attnos        don't need to be changed.

StoR Scans

StoR scans are inserted for every windowing clause that is specified ina query. Usually, a StoR will sit on top of a Stream Scan or a result ofa subquery or a view. The following happens in StoR scan folding:

-   -   1. Create a new StoR Scan plan item (if it does not exist        already)    -   2. Since a StoR scan will only have a Shared Scan as its child,        we will first cap the only varno transform for that query block        (the one we created while folding Shared Scan).    -   3. We will add a varno transform from the underlying shared        scan's scanrelid to the varno of the StoR plan item that we just        created. All references to the vars which were coming from the        scan under the StoR will now be changed to the StoR scan's        varno. This transform will be effective from root of the plan        tree to the StoR scan item we just created. We have the capped        transform (from 2 above) effective below this node.    -   4. No at tno transformations are required because the data is        coming from only one source so at tnos don't need to be changed.

create stream r1(a int, b int cqtime user) type unarchived; explainselect * from r1 <slices ‘3’>; QUERY PLAN----------------------------------------------------------- Result (1) (cost=0.00..0.00 rows=0 width=0)   ->  StoR Scan (1.1)  < SLICES ‘3’ > (cost=0.00..0.00 rows=0 width=0)     ->  Stream Scan (1.1.1)  on r1 (cost=0.00..31.40 rows=2140 width=8) FOLD EXPLAIN:  INPUT RANGE TABLE:   (1) RTE: (Kind:RTE_RELATION) (relid:81933) (alias:r1) (Rels:)  ATTNOTRANSFORMS:  VARNO TRANSFORMS:    [1] (old_varno:1) (new_varno:1)(start_at_node:1.1.1)    [2] (old_varno:1) (new_varno:2)(stop_at_node:1.1.1)  QUAL ITEMS:  PLAN ITEMS:    [1] (SCAN_ITEM)    (plan:[1.1.1] STREAMSCAN) (varno:1) (rte_is_new:t)    StoRDescriptor: NULL     RTE: (Kind:RTE_RELATION) (relid:81933)(alias:r1) (Rels:)    [2] (STOR_ITEM)     (plan:[1.1] STORSCAN)(varno:2) (rte_is_new:t)     StoRDescriptor: (SD_CHUNKING)(partitions:0) (fragments:0)    RTE: (Kind:RTE_UNKNOWN) (Rels: 1)    [3](RESULT_ITEM)     (plan:[1] RESULT) (varno:3) (rte_is_new:t)    StoRDescriptor: (SD_CHUNKING) (partitions:0) (fragments:0)     RTE:(Kind:RTE_RESULT) (Rels: 2) ======================================== (26rows)

Subqueryscans

In case of a streaming subqueryscan plan node, a shared subquery planitem may be generated. We do the folding for the query block that ishanging off of the subquery just like it were a top level query blockand then append all the transform (varno and at tno) information to themain queries context which keeps all the transformation information. Wealso add a varno transformation from the scanrelid of the subqueryscanto the output of the already folded subplan's top level result item.

Mixed Joins

Mixed joins are a place where a new heap tuple component may be added tothe CqTuple. This component is actually the join heap tuple (with thetargetlist containing vars from the outer and the inner side of thejoin) that will be augmented to the incoming CqTuple. Mixed join code iscapable of handling inline tables too. The folding part specific toinline tables will be covered in the inline table specification. In caseof a mixed join with a streaming component as the outer and anon-streaming subquery as the inner, the plan folding may be done in thefollowing way:

-   -   1. Create a Plan item for the Join plan node.    -   2. Create a Plan item for the subplan (the inner side of the        join)    -   3. Gather all the varnos (by traversing recursively) of the        inner subplan. These are the scanrelids of the non-streaming        relations in the inner subplan.    -   4. For each of these varnos, create a varno transform from the        varno to the new varno of the subplan shared item createdin step        2 above.    -   5. There could be expressions in the targetlist which will have        a varno of 0, so we may transform them to the new varno of the        subplan shared plan item (also known as O-varno-transforms). But        there could already be O-varno-transform (say it there was a        mixedjoin as the outer child of this current mixed join). We may        cap these O-varno-transforms to be effective only from the outer        plan of the mixed join.    -   6. Now, we add a O-varno-transform from 0 to the sunplan shared        item's varno. This will be effective from the root of the plan        tree till the join node.    -   7. The join heap tuple (that will be augmented to the CqTuple),        will have varnos from both inner and outer. Hence the attnos for        these vars will be relative to the inner or the outer. We add        attno transforms so that their attnos are correct when referred        to the new varno that represents the entire join heaptuple. We        may, for example, not alter the attnos of any vars that come        from the outer since these vars will be accessed from the outer        part of the CqTuple. We add attno transforms for all inner vars        in the following way. If the oldattno is non-zero (regular vary,        then we change it to the position in the targetlist of the join.        However, if the oldattno is zero, signifying it is an        expression, there could be more than one expression and then we        would wipe out the effects of all but the last attno transform.        As such, a transform from 0 to the index in the target list may        not be added. Hence, if the oldattno is zero then we copy the        varattno to the oldattno field as a part of the attno transform.

create stream s1 (a int, b int, c int cqtime user) type unarchived;create table t1 (a int, b int); create index t1_a_idx on t1 (a); explainselect s.c, s.a, (s.a + s.c) as sum1,           t.a, t.b, (t.b + s.c) assum2        from s1 s, t1 t where s.a = t.a order by 6; QUERY PLAN------------------------------------------------------------------------------------------------------  Result (1) (cost=2790.96..2842.85 rows=20758 width=16)   ->  Sort (1.1) (cost=2790.96..2842.85 rows=20758 width=16)     Sort Key: ((t.b + s.c))    ->  Nested Loop (1.1.1)  (cost=0.00..1302.47 rows=20758 width=16)      ->  StoR Scan (1.1.1.1)  < SLICES 1 ROWS > (cost=0.00..0.00 rows=0width=0)         ->  Stream Scan (1.1.1.1.1)  on s1 s (cost=0.00..29.40rows=1940 width=8)       ->  Index Scan (1.1.1.2)  using t1_a_idx on t1t  (cost=0.00..0.47 rows=11 width=8)         Index Cond: (t.a = s.a) FOLD EXPLAIN:   INPUT RANGE TABLE:   (1) RTE: (Kind:RTE_RELATION)(relid:81936) (alias:s) (Rels:)   (2) RTE: (Kind:RTE_RELATION)(relid:81939) (alias:t) (Rels:)  ATTNO TRANSFORMS:    [1] (varno:0oldattno:0 newattno:0)  (stop:1.1.1.1)    [2] (varno:2 oldattno:1newattno:4)  (stop:1.1.1.1)    [3] (varno:2 oldattno:2 newattno:5) (stop:1.1.1.1)    [4] (varno:0 oldattno:0 newattno:0)  (stop:1.1.1.1) VARNO TRANSFORMS:    [1] (old_varno:1) (new_varno:1)(start_at_node:1.1.1.1.1)    [2] (old_varno:1) (new_varno:2)(stop_at_node:1.1.1.1.1)    [3] (old_varno:2) (new_varno:3)(start_at_node:1.1.1.2)    [4] (old_varno:2) (new_varno:5)(stop_at_node:1.1.1.2)    [5] (old_varno:0) (new_varno:5)(stop_at_node:1.1.1.2)  QUAL ITEMS:  PLAN ITEMS:    [1] (SCAN_ITEM)    (plan:[1.1.1.1.1] STREAMSCAN) (varno:1) (rte_is_new:t)    StoRDescriptor: NULL     RTE: (Kind:RTE_RELATION) (relid:81936)(alias:s) (Rels:)     [2] (STOR_ITEM)     (plan:[1.1.1.1] STORSCAN)(varno:2) (rte_is_new:t)     StoRDescriptor: (SD_CHUNKING)(partitions:0) (fragments:0)     RTE: (Kind:RTE_UNKNOWN) (Rels: 1)   [3] (SCAN_ITEM)     (plan:[1.1.1.2] INDEXSCAN) (varno:3)(rte_is_new:t)     StoRDescriptor: NULL     RTE: (Kind:RTE_RELATION)(relid:81939) (alias:t) (Rels:)    [4] (JOIN_ITEM)     (plan:[1.1.1]NESTLOOP) (varno:4) (rte_is_new:t) (parent:5)     StoRDescriptor:(SD_CHUNKING) (partitions:0) (fragments:0)     RTE: (Kind:RTE_JOIN)(jointype:JOIN_INNER) (Rels:)    [5] (SUBPLAN_ITEM)     (plan:[1.1.1.2]INDEXSCAN) (varno:5) (rte_is_new:t) (subplan:4)     StoRDescriptor: NULL    RTE: (Kind:RTE_SUBQUERY) (Rels:)    [6] (RESULT_ITEM)     (plan:[1]RESULT) (varno:6) (rte_is_new:t)     StoRDescriptor: (SD_CHUNKING)(partitions:0) (fragments:0)     RTE: (Kind:RTE_RESULT) (Rels: 2 5)======================================== (51 rows)

Shared Aggs

Shared Agg items are created when an agg or an agg sitting on top of oneor more of the following plan nodes is encountered:

Unique

Sort

Limit

Group

An agg node combined with zero or more of these plan nodes may form an“agg chain.” We will create a shared plan item for the entire agg chain.The output of an agg is a combination of grouping columns (optional—onlyfor grouped aggregates) and agg refs (for grouped and ungroupedaggregates). All input varno and attno references may be changed in thefollowing way:

-   -   1. An agg plan item is created.    -   2. The existing transformations are capped so that they are only        effective below the agg chain.    -   3. All agg refs have a varno of 0. A varno transform is added        from 0 to the varno of the newly created agg plan item.    -   4. For these agg refs, we also are add attno transform to copy        the varattno field to varoldattno in the vars.    -   5. For grouping columns, the old varnos for the input to the agg        chain may be pulled and a varno transform for each of these        varnos may be created. These varno transforms will be from the        oldvarno to the new varno of the shared agg item    -   6. For all the old varnos of the grouping columns we add attno        transforms to copy the varattno field to the varoldattno field.    -   7. All these transformations mentioned above (varno and attno)        will be effective from root of the plan tree till the start of        the agg chain.

explain select a, count(*) from r1 <slices ‘3’> group by a; QUERY PLAN-------------------------------------------------------------------------------------  Result (1)  (cost=0.00..2.50 rows=200width=0)   ->  HashAggregate (1.1)  (cost=0.00..2.50 rows=200 width=0)    ->  StoR Scan (1.1.1)  < SLICES ‘3’ > (cost=0.00..0.00 rows=0width=0)       ->  Stream Scan (1.1.1.1)  on r1 (cost=0.00..31.40rows=2140 width=4)  FOLD EXPLAIN:  INPUT RANGE TABLE:    (1) RTE:(Kind:RTE_RELATION) (relid:81933) (alias:r1) (Rels:)  ATTNO TRANSFORMS:   [1] (varno:0 oldattno:0 newattno:0)  (stop:1.1)    [2] (varno:1oldattno:0 newattno:0)  (stop:1.1)  VARNO TRANSFORMS:    [1](old_varno:1) (new_varno:1) (start_at_node:1.1.1.1)    [2] (old_varno:1)(new_varno:2) (start_at_node:1.1) (stop_at_node:1.1.1.1)    [3](old_varno:0) (new_varno:3) (stop_at_node:1.1)    [4] (old_varno:1)(new_varno:3) (stop_at_node:1.1)  QUAL ITEMS:  PLAN ITEMS:    [1](SCAN_ITEM)      (plan:[1.1.1.1] STREAMSCAN) (varno:1) (rte_is_new:t)    StoRDescriptor: NULL     RTE: (Kind:RTE_RELATION) (relid:81933)(alias:r1) (Rels:)    [2] (STOR_ITEM)      (plan:[1.1.1] STORSCAN)(varno:2) (rte_is_new:t)     StoRDescriptor: (SD_CHUNKING)(partitions:0) (fragments:0)     RTE: (Kind:RTE_UNKNOWN) (Rels: 1)   [3] (AGG_ITEM)      (plan:[1.1] AGG) (varno:3) (rte_is_new:t)(stop:[1.1] AGG)     StoRDescriptor: (SD_CHUNKING) (partitions:0)(fragments:0)     RTE: (Kind:RTE_AGGREGATE) (Rels: 2)     [4](RESULT_ITEM)      (plan:[1] RESULT) (varno:4) (rte_is_new:t)    StoRDescriptor: (SD_CHUNKING) (partitions:0) (fragments:0)     RTE:(Kind:RTE_RESULT) (Rels: 3) ======================================== (35rows)

Shared Result

Shared Result items are created when we encounter either a result nodeor a result node sitting on top of one or more of the following plannodes:

Unique

Sort

Limit

Group

A result node combined with zero or more of these plan nodes maycomprise an “result chain.” We will create a shared plan item for theentire result chain. Result nodes can be either on the top of the queryplan, or introduced in the middle of a query block for projectionpurposes. For a top level result node, we do not need anytransformations. But if a result node is not the topmost node of a queryblock we may do the following:

-   -   1. Cap all the existing varno transforms so that they are        effective only below the result chain.    -   2. Pull all the old varnos of the vars from the targetlist of        the result node. Add a varno transform for each of these old        varnos. The new varno will be the varno of the newly created        Result shared planitem.    -   3. Add an extra varno transform (0-varno-transform) from 0 to        the varno of the result shared plan item for any exprs, consts        etc.    -   4. Add attno transforms for the columns since we have mostly        done projections in this result node. If the oldattno is        non-zero (regular vary, then we change it to the position in the        targetlist of the join. However, if the oldattno is zero,        signifying it is an expression, it will be wrong to add a        transform from 0 to the index in the targetlist because there        could be more than one expressions and then we would wipe out        the effects of all but the last attno transform. Hence, if the        oldattno is zero then we copy the varattno to the oldattno field        as a part of the attno transform.    -   5. All these transformations mentioned above (varno and attno)        will be effective from root of the plan tree till the start of        the result chain.

create stream res(a int, b int, t timestamp cqtime user) typeunarchived; explain select * from res <slices 1 rows> where a = 3 limit3; QUERY PLAN--------------------------------------------------------------------------------------------  Result (1) (cost=0.00..0.00 rows=1width=0)   ->  Limit (1.1)  (cost=0.00..0.00 rows=1 width=0)     -> Result (1.1.1)  (cost=0.00..0.00 rows=0 width=0)       ->  StoR Scan(1.1.1.1)  < SLICES 1 ROWS > (cost=0.00..0.00 rows=0 width=0)        Filter: (a = 3)         ->  Stream Scan (1.1.1.1.1)  on res(cost=0.00..32.12 rows=9 width=16)  FOLD EXPLAIN:  INPUT RANGE TABLE:   (1) RTE: (Kind:RTE_RELATION) (relid:81947) (alias:res) (Rels:)  ATTNOTRANSFORMS:    [1] (varno:1 oldattno:1 newattno:1)  (stop:1.1.1)    [2](varno:1 oldattno:2 newattno:2)  (stop:1.1.1)    [3] (varno:1 oldattno:3newattno:3)  (stop:1.1.1)  VARNO TRANSFORMS:    [1] (old_varno:1)(new_varno:1) (start_at_node:1.1.1.1.1)    [2] (old_varno:1)(new_varno:2) (start_at_node:1.1.1) (stop_at_node:1.1.1.1.1)    [3](old_varno:1) (new_varno:3) (stop_at_node:1.1.1)    [4] (old_varno:0)(new_varno:3) (stop_at_node:1.1.1)  QUAL ITEMS:    [1] QualItem(plan:1.1.1.1)  PLAN ITEMS:    [1] (SCAN_ITEM)     (plan:[1.1.1.1.1]STREAMSCAN) (varno:1) (rte_is_new:t)     StoRDescriptor: NULL     RTE:(Kind:RTE_RELATION) (relid:81947) (alias:res) (Rels:)    [2] (STOR_ITEM)    (plan:[1.1.1.1] STORSCAN) (varno:2) (rte_is_new:t)    StoRDescriptor: (SD_CHUNKING) (partitions:0) (fragments:0)     RTE:(Kind:RTE_UNKNOWN) (Rels: 1)    [3] (RESULT_ITEM)     (plan:[1.1.1]RESULT) (varno:3) (rte_is_new:t)     StoRDescriptor: (SD_CHUNKING)(partitions:0) (fragments:0)     RTE: (Kind:RTE_RESULT) (Rels: 2)    [4](RESULT_ITEM)     (plan:[1] RESULT) (varno:4) (rte_is_new:t)    StoRDescriptor: (SD_CHUNKING) (partitions:0) (fragments:0)     RTE:(Kind:RTE_RESULT) (Rels: 3) ======================================== (39rows)

Orchestrating Iterator-Model Sub-Plans for Streaming

A technique that permits a quick and easy reuse of RDBMS infrastructurein an SRDBMS is disclosed as follows:

The SRDBMS evaluates standard relational operations (e.g., filters,joins, aggregates, sort) on streaming data using the special CQoperators. Although these CQ operators are conceptually similar to theIMP operators of a traditional RDBMS their underlying interfaces aresignificantly different. An SRDBMS can achieve streaming versions ofstandard relational operations by reusing the standard IMPimplementation of the underlying RDBMS, and goes beyond just reusingsmaller components such as an expression evaluator—the idea is to takefull advantage of mature and efficient technology such as outer joinimplementation.

In essence, a CQ operator focuses on managing streaming data as well assharing information and orchestrates the underlying IMP sub-plan toachieve the actual relational operations.

The techniques to address the situation posed above as described abovemay include but is not limited to the following:

-   -   As part of plan folding the input IMP plan is chopped up into        various sub-plans, each of which is placed under the control of        a specific CQ operator. In a sense, each CQ operator now        contains an independent IMP executor.    -   A new leaf node, an Adapter, is added to the IMP sub-plan. The        Adapter is an iterator-model operator and serves as a way to        bridge the CQ operator and the IMP sub-plan. In essence, the        Adapter fetches one or more CQ tuples from the controlling CQ        operator and delivers it to its parents.    -   As a result of plan folding, the expressions evaluated by the        operators in the IMP sub-plan can work seamlessly on a composite        CQ tuple in most cases. In certain circumstances, however, an        IMP operator may access the attributes of an input tuple        directly and not through the expression evaluator. In such        situations the Adapter projects the composite CQ tuple into a        traditional tuple so that it can be evaluated by the IMP        sub-plan.    -   When the CQ operator is called with a new CQ tuple, it evaluates        the appropriate logic (e.g., has a window edge has been        triggered) and does one or more of the following depending on        the actual operator:        -   Buffer the CQ tuple in local state        -   Call the next( ) method on the root of the IMP sub-plan and            deliver one or more tuples through the Adapter

Mixed Join    -> NestedLoop     -> Adapter     -> IndexScan

Example of CQ Operator Orchestrating IMP Sub-Plan with Buffering

For instance, consider the example of a stream-table “Mixed Join” thatis shown above. The heavy lifting of the actual join happens in theNested Loop operator, but the outer of the Nested Loop is the Adapterwhich in turn fetches one or more CQ tuples at a time from the MixedJoin operator. In this example the Mixed Join does not need to bufferany input CQ tuple and can instead just call the next( ) method on theNested Loop operator.

Mixed Join   -> Merge Left Join (cost=299.56..653.73 rows=22898 width=8)    Merge Cond: (f.b = b.c)    -> Sort (Sort Key: f.b)      -> Adapter   -> Sort (Sort Key: b.c)     -> Seg Scan on bar b

Example of CQ Operator Orchestrating IMP Sub-Plan with Buffering

Suppose, however, the join in question is an outer join. In such ascenario the join operator will be a Merge Join and the inner and outerarms need a Sort as shown above. Since the outer arm has a Sort above anAdapter, in this situation the Mixed Join operator needs to buffer upthe CQ tuples that form part of a window before they are consumed by theAdapter.

Shared Agg   -> HashAgg    -> Adapter

Example of CQ Operator Orchestrating IMP Sub-Plan with AccumulationAdvanced Solution Device

This process may be optimized for certain operations that may be foundin CQ queries, namely accumulative operations for non-overlappingwindows. For instance, consider a situation where we are computing agrouped aggregate over a chunking window on a stream as part of a CQ asshown above. In this situation there is no need to actually buffer upthe individual CQ tuples if we can find a way to accumulate the tuplesin the underlying HashAgg operator. This optimization can apply tovarious different IMP operators such as aggregates as well as sortoperators and may be accomplished in the following fashion:

-   -   Introduce a new method called accumulate( ) to the standard        iterator model interface. The accumulate( ) method operates in a        fashion similar to next( ). by obeying the following contract:        -   Unlike next( ), the accumulate( ) method only fetches a            single tuple from its input.        -   Like next( ), the accumulate( ) dispatcher checks to see if            any parameter of the operator depends on has changed. If so,            the dispatcher calls rescan( ) on the operator.    -   When the CQ operator receives a new CQ tuple it calls        accumulate( ) on the root of its associated IMP sub-plan which        in turn fetches the input CQ tuple via the Adapter at the leaf        of the IMP sub-plan.    -   When a window boundary is triggered the CQ operator repeatedly        calls next( ) on the IMP sub-plan in order to produce the        results of the operator for the window. If, as in the above        example, the IMP operator is a hashed aggregate then as part of        calls of next( ) the aggregates are “finalized” for use in        result tuples.    -   Introduce HARD_RESET and SOFT_RESET, two special built-in        parameters that are used by a CQ operator to communicate window        boundary events with an IMP operator. In particular, HARD_RESET        applies to chunking windows where it indicates that the IMP        operator can clean out its state entirely. In contrast,        SOFT_RESET is typically used with landmark windows in order to        indicate to the IMP operator that its actual underlying internal        state (based on user data) should be preserved although the        associated controlling data structures (e.g., list/hash-table        iterators) can be reset.

The accumulating device described above can also be used for operatorssuch as Sort and Bounded Sort in addition to Aggregates.

Windowing Infrastructure

The following set of techniques may be used to implement variousdifferent types of StoRs (StreamToRelation operators or windows) in aunified manner.

The streaming query processing includes an ability to divide theinfinite stream of data into “windows” and perform different kinds ofoperation on them. In an SRDBMS, we achieve this by converting thestream into window sized relations and then performing all otheroperations on these relations. We call the operators that dissect thestream into windows as StoRs (for Stream to Relation).

A unified windowing infrastructure may solve the following:

-   -   Support multiple kinds of windows (chunking, sliding, landmark)        whose parameters can in turn be based on different concepts        (time, rows, windows).    -   Eliminate additive latency. When CQs are stacked up in a chain,        the prior art implementation results in a latency that is the        sum of the advance intervals of each successive query in the        chain.

The techniques to address the situation posed above as described abovemay include but is not limited to the following:

Data and Control Tuples

The raw stream is a sequence of data tuples. The way we choose toimplement StoRs is by punctuating the tuples of the raw stream withspecial control information that will denote the window edges. Thesetuples are called Control tuples. Therefore, the StoR operators willusually be the first operator that tuples from a raw stream will go to.Once the raw stream has passed through the StoR, it will contain data aswell as control tuples. There could be multiple StoRs in a query (if wehave a view, a subquery etc.). In such cases, the stream that containsthe data and control tuples can pass through one or more StoRsdownstream. Therefore, the StoRs are equipped to handle data as well ascontrol tuples in their incoming stream while they inject their controltuples.

Window Edges and their Advance Kinds

Ever window has two edges—the leading edge and the trailing edge. Theheart of the operation of a StoR is to detect when an edge is triggeredso that it can insert a control tuple signifying the existing of anedge. The advancement of these edges can be based either on time (thenon-decreasing value in cqtime column of the stream), or on number ofrows, or on the number of windows seen coming from an underlyingsubquery. We will call these different advance kinds as advance-by-time,advance-by-row and advance-by-windows respectively.

All three types of windows (landmark, chunking and sliding) may have thesame mechanism of triggering the leading edge (specified by the ‘slices’clause in chunking windows or ‘advance’ clause in landmark and slidingwindows). They will, however, differ a little on when their trailingedges are inserted.

Edge Triggering Logic

The basic edge triggering logic includes comparing the value of anattribute (for advance-by-time it is the cqtime column, foradvance-by-rows it is the rowcount, for advance-by-windows it is thewindow id) of a new incoming tuple with the value of the next edge. Allthe tuples with the value of the attribute less than or equal to thevalue of the next edge belong to the window that is being formed. Theedge triggering logic differs slightly based on the advance kind of theedge. When we are advancing by time, we use the value of the cqtimeattribute of the stream whereas, when advancing by rows we use therowcount. While any data tuple can have the same value of cqtime as itsprevious one, it may not have the same rowcount. Therefore, foradvance-by-time, we cannot trigger an edge unless we have seen a tuplethat has a cqtime that is greater than the value of the next edge. Incontrast, when we are advancing by rows, we can trigger an edge when wesee a tuple with rowcount that is equal to the next edge of the window.For advance-by-windows, we compare the window id in the control tuplewith the value of the next edge. Since two windows may not have the samewindow id, it is similar to advance-by-rows in that we can close awindow in a superquery that is advancing by windows if we see a controltuple from a subquery that denotes a leading edge and that has a windowid equal to the next edge of the superquery's StoR.

Following is an exemplary algorithm for triggering an edge:

bool edge_triggered(next_edge,          input_tuple_attribute,         input_tuple_type) /*  * the input_tuple_attribute can be cqtimeof the input tuple for  * advance-by-time or rowcount of the input tuplefor  * advance-by-rows. It will be the window id of incoming tuple  *for advance-by-windows.  */ {  if (GetAdvanceKind(next_edge) ==advance-by-time)  {   if (input_tuple_attribute > GetValue(next_edge))    return true;  }  else if (GetAdvanceKind(next_edge) ==advance-by-rows)  {   Assert(input_tuple_attribute <=GetValue(next_edge));   if (input_tuple_attribute ==GetValue(next_edge))     return true;  }  else /* advance-by-windows */ {   Assert(input_tuple_attribute <= GetValue(next_edge));   if(IsLeadingEdgeControlTupleType(input_tuple_type) &&    input_tuple_attribute == GetValue(next_edge))     return true;  } return false; }

StoR Execution Logic for Leading Edge

As mentioned earlier, the execution logic for leading edge may be thesame for all the types of windows. When we have advance-by-time, if weget an input tuple that does not trigger the edge, we route it along tothe downstream operators. If, however, a tuple comes after some time, itcan trigger one or more edges and we send control tuples representingall the intermediate leading edges and then route the tuple todownstream operators. The input tuple is routed after the controltuple(s) because it has a timestamp greater than the window edge that weare closing so it belongs to the next window. For advance-by-rows, if weget an input tuple that does not trigger an edge, we route it along tothe downstream operators. If we get a tuple that triggers an edge (wemay trigger one edge in advance-by-rows or advance-by-windows), we routethe data tuple and then route the control tuple signifying end of thewindow. This is because the input tuple belongs to the window that weare about to close.

Following is the algorithm for StoR execution of leading edge:

void execute_StoR (stor_state,          input_tuple) {  control_tuple  = NULL; input_tuple_type = GetTupleType(input_tuple); next_edge =GetLeadingEdge(stor_state); /* Get input_tuple_attribute */ if(GetAdvanceKind(next_edge) == advance-by-rows)   input_tuple_attribute =GetRowCount(input_tuple); else if (GetAdvanceKind(next_edge) ==advance-by-time)   input_tuple_attribute = GetCqTime(input_tuple); else  input_tuple_attribute = GetWindowId(input_tuple); while(edge_triggered (next_edge,           input_tuple_attribute,          input_tuple_type)    ) {  ContructControlTuple(control_tuple); if (GetAdvanceKind(next_edge) == advance-by-time)  {   RouteTuple(control_tuple);    control_tuple = NULL;  } AdvanceEdge(next_edge); } /* end of edge_triggered while loop */ /*Route data tuple */ if (IsDataTupleType(input_tuple_type))  RouteTuple(input_tuple);   /* Route control tuple in case of advanceby rows/windows */   if (control_tuple != NULL)   RouteTuple(control_tuple);      }StoR Execution logic for Trailing Edges

The various window types differ in how the trailing edges of theirwindows are emitted. Following is the execution logic specific to eachwindow type.

Chunking Windows

Chunking windows, by definition are contiguous. Hence, there is no needto explicitly emit a trailing edge, because the leading edge of windowcan be interpreted as the trailing edge of the next window.

The trailing edge of a chunking window will have the same advance kindas the leading edge.

A data tuple cannot be a part of more than one chunking window.

Landmark Windows

The trailing edge of a landmark window is triggered based on the inputtuple's relevant attribute (cqtime for advance-by-time, rowcount foradvance-by-rows, and window_id for advance-by-windows) with respect tothe next edge calculated by what was specified in the RESET clause.

The trailing edge triggering logic is very similar to the leading edgelogic except that it works independently and orthogonal to the leadingedge. So, an input tuple can trigger one or both of the edges.

The trailing edge of a landmark window can have an advance kind that isdifferent from its leading edge.

A data tuple may be a part of multiple windows because all windows willhave different leading edges but the same trailing edge until a trailingedge (RESET) is triggered.

Sliding Windows

For sliding windows, trailing edges cannot be triggered independently(unlike landmark windows), because the trailing or ‘visible’ edge of awindow is calculated backwards from the leading or ‘advance’ edge andthe advance kind of each edge can be different. Since, trailing edge ofa window should precede the leading edge, but we cannot emit a trailingedge till we are about to emit a leading edge, we encode the informationabout the trailing edge in the control tuple that is emitted for theleading edge. The downstream operators are responsible for buffering thetuples and extracting the embedded information about the trailing edgefrom the control tuple. This information can be used to discard thebuffered tuples that don't belong to the window.

A data tuple can be part of multiple sliding windows.

Depending on the advance kind of the visible clause, we embed thetrailing edge information inside the control tuple differently.

-   -   If the trailing edge is advance-by-rows then when we emit the        control tuple for the leading edge we populate the trailing edge        information field with (R−V) where, R is the rowcount of the        control tuple for leading edge and V is the number of rows        specified in the visible clause.    -   If the trailing edge is advance-by-time then when we emit the        control tuple for the leading edge we populate the trailing edge        information field with (T−V) where T is the cqtime of the        control tuple for leading edge and V is the time specified in        the visible clause.

Solution for the Additive Lag Problem

When we are advancing by time, we cannot trigger a window edge until wehave seen a tuple that has a cqtime greater than the next edge value.This can potentially lead to an additive lag in some cases when we haveboth subqueries and superqueries advance by time.

Example: Let us say that the subquery StoR is slices ‘3 seconds’ and thesuperquery StoR is slices ‘5 seconds’. The windows from the subquerywill be 3 seconds apart. Let us assume that the rows from the subquerycome at times 3, 6, 9 . . . seconds. The superquery will close its firstwindow (next edge being 5) when it sees the first tuple with timestamp6. Though the subquery finished its 3 second window, the superquerycould not close its 5 seconds window because it did not see any tuplewith cqtime greater than 5. The superquery had to wait till the subquerywas done finishing the 6 second window to close its 5 second window.Thus, we had a lag of 3 seconds. If we have many levels of nesting, thislag could very quickly add up making the streaming applicationsprohibitively slow.

This problem of additive lag is solved by having speculative time incontrol tuples.

Speculative Time in Control Tuples

In order to reduce the additive lag described above, we introduced aconcept of adding a speculative time in the control tuples for leadingedges of advance-by-time windows. When we emit a leading edge, we send acontrol tuple with cqtime as the window edge time. We also populate anextra field in the control tuple—called the speculative time with thevalue of the next window edge. By doing this, we are informing theconsumer of this control tuple that the next data that comes out of thesubquery will have a cqtime of the next window edge. In the aboveexample, the control tuple ending the 3 second window will have aspeculative time of 6. When the superquery, sees a control tuple withspeculative time of 6 it knows that it cannot see any data that willhave a cqtime less than or equal to 5 hence it can close the 5 secondwindow as soon as the subquery's 3 second window has ended.

The edge triggering logic in advance-by-time windows will changeslightly. When we are advancing by time, if we get an input tuple thatis a control tuple we use the speculative time as theinput_tuple_attribute instead of the cqtime of the tuple to check if anedge can be triggered.

a. Visualization Infrastructure

A mechanism that enables users to configure rich and complex dashboardsthat are driven by the results of continuous queries is disclosedherein.

An SRDBMS can evaluate a CQ over a stream and produce an output streamof data that can be accessed one window at a time through a cursor. Theusers may be able to construct a dashboard organized with a set ofwidgets (e.g., line charts, pie charts etc.) that are each driven by oneor more CQs, and are continuously updated in real time on an internetclient.

There are other aspects that are relevant. Some of these include:

-   -   1. The ability for certain visualizations such as line charts to        include recent historical data. Therefore, the solution may        manage the history, as well as be able to start with recent        history “baked in” when a new client signs on.    -   2. The ability for a visualization to render a restricted subset        of the data produced by a CQ, perhaps based on a parameter        specified a user.    -   3. The ability to add customized, user-provided visualization        widgets such as a new composite bar-pie chart.

An approach to address the situation described above is to build aweb-based application that can be deployed in multiple embodiments suchas a thick client, or a plugin that can run in a web browser. In eitherevent, the client/plugin may be capable of evaluating scripting logic,communicating data to and from the server, and have an associatedcharting library that can be used to render the data. For the remainderof this section we will use the term plugin to refer to eitherembodiment.

There are two components that are included in the solution: themiddle-tier server application and the client that can be downloaded andexecuted by the plugin. There are two kinds of data that are exchangedbetween the client and the server:

1. Requests from the client (e.g., subscriptions to a particular datafeed)

2. Data corresponding to a given subscription.

We now describe the visualization specification, the server and theclient components in more detail.

Visualization Description

The visualization widgets may be specified in XML files that arecompiled into scripts that are evaluated in a plugin. A widget specifiedin this fashion can support different styles of charts (e.g., line, bar,area etc.). In addition, it is possible to create a widget with morethan one component. For example, it could contain a quadrant with 4different charts, or it could contain a side-by-side view of a chart andits corresponding data-table. The XML format for creating newvisualizations is broken down into the following three sections:

-   -   Parameters—defines any pieces of data that need to be collected        from the user    -   Datasources—identifies the queries that power the visualization    -   Components—defines the visual elements themselves, such as        chart, table, etc

The overall structure of a visualization specification may be asfollows:

<visualization title=“my title”>   <parameters>     <parameter/> (zeroor more)   </parameters>   <datasources>     <datasource/> (one or more)  </datasources>   <components layout=“tab|grid|vertical|horizontal”>    <table title=“mytitle”> (zero or more)     <chart title=“mytitle”>(zero or more)   </components> </visualization>

Parameters

The <parameters> element is optional and it can contain 1 or more<parameter> element. Each <parameter> element may be presented to theuser as a question/field prior to launching the actual visualization.The parameter element accepts the following attributes:

-   -   name—this is required and refers to the variable-name under        which the parameter will be stored    -   label—this is optional and will be used as on the screen which        is shown to the user. If left empty, the system will use the        value of ‘name’    -   defaultValue—this value is optional and it will be used to        populate the initial form when it is presented to the user

Example

<parameters>   <parameter name=“history”        label=“Datapoints inchart”        defaultValue=“200”   <parameter name=“title”       label=“Chart title”        defaultValue=“Total Shares Traded”/></parameters>This would result in the prompt as shown in FIG. 4.

Datasources

The <datasources> element should contain 1 or more <datasource>elements. The datasource element accepts the following attributes:

-   -   dataSourceId—accepts any value unique amongst all the        datasources    -   queryId—refers to one of the queries that is running on the        server    -   history—refers to the number of data-elements to retain in        memory. When the full history has been reached, the oldest        pieces of data may be evicted from memory as new ones are added

Example

<datasources>   <datasource       dataSourceId=“1”      queryId=“TOTAL_SHARES_QUERY”       history=“50”>   </datasource>  <datasource       dataSourceId=“2”       queryId=“VWAP_QUERY”      history=“50”>   </datasource> </datasources>

Components

The <components> element should contain 1 or child-elements of types:<chart> or <table>.

Chart

The <chart> element can have the following attributes:

-   -   xAxisTitle: label which shows up next to the x-axis. This        attribute is optional    -   yAxisTitle: label which shows up next to the left-most y-axis.        This attribute is optional    -   xAxis2Title: label which shows up next to the right-most y-axis.        This attribute is optional    -   xAxisType: can be one of: date, time, category, log and/or        numeric. If the attribute is set to category, then two        additional attributes are required (categoryDataSourceId and        categoryField).

In addition, the chart has a child-element called <series>. The <series>element may contain 1 or more child-elements, each of which may definean individual series of the chart. The series element can be one of:bar, area, line, plot and bubble. Each series element can have thefollowing attributes:

-   -   y: defines a column name from the query which will be used for        the y-value    -   x: defines a column name from the query which will be used for        the x-value    -   dataSourceId: refers to one of the datasources which is defined        in the <datasources> section    -   label: Optional attribute which will be used in the tooltip of        the chart    -   axis: accepts values 1 or 2. The value 1 is the default value if        it is empty. A value of 1 indicates that the series is tied to        the left-most y-axis, and a value of 2 indicates that the axis        is tied to the right-most y-axis    -   color: hexadecimal value in this format: 0x000000

If the series is of type bubble, the user may also supply a column-namefor z (refers to the size of the bubble).

Example

<chart xAxisTitle=“Time”     yAxisTitle=“Y1”     yAxis2Title=“Y2”    xAxisType=“datetime|category|log|numeric”>     <series>      <area       y=“volume”        x=“windowtime”        label=“Volume”       dataSourceId=“1”>      </area>      <line        y=“price”       x=“windowtime”        label=“Price”        dataSourceId=“1”>     </line>      <bar        y=“volume”        x=“windowtime”       label=“Volume”        dataSourceId=“1”>      </bar>      <plot       y=“volume”        x=“windowtime”        label=“Volume”       dataSourceId=“1”>      </plot>      <bubble        y=“volume”       z=“price”        x=“windowtime”        label=“Volume”       dataSourceId=“1”>      </bubble>     </series> </chart>

Table Example

<table title=“Table” dataSourceId=“1”>   <columns>     <columnname=“vwap” label=“VWAP”/>     <column name=“windowtime” label=“Time”format=“datetime”/>   </columns> </table>

Server Application

The middle-tier application can be deployed in a generic applicationserver, such as one that supports standard J2EE applications. Theapplication is capable of performing at least the following roles:

-   -   Instantiate a CQ in the SRDBMS either on-the-fly or on startup        based on a configuration that can be fetched from a standard        location such as the file system or a database.    -   Respond to the following requests from clients:        -   Fetch the application that can be downloaded and run in the            plugin.        -   Fetching the list of available visualizations from the            configuration location.        -   Fetching a specific named visualization as an XML document.        -   Establishing a subscription for a CQ.        -   Backfill a specified amount of recent history of windows for            a CQ.    -   Pushing windows worth of data from CQs to the clients based on        the available subscriptions.

Client Application

The client application gets downloaded into the plugin by accessing adesignated URL. The application offers a set of “tabs” that provide thefollowing different functionality to the end user:

-   -   A report gallery that shows a list of available visualizations        and lets users add a visualization on the fly.    -   A dashboard that contains various activated visualizations.    -   An admin panel that shows the list of available queries and lets        users add a query on the fly.

The general mode of operation is for the client to startup in the“report gallery” tab and for the user to pick a specific visualizationwidget to be activated and added to the dashboard. When a visualizationwidget is activated, the client does the following:

-   -   Fetch the XML document specifying the visualization from the        server.    -   Parse the document and identify the three major aspects of the        visualization: its parameters, its data sources, and its        components. As part of this process, the client does the        following:        -   Process the parameters by displaying dialog boxes so that            the user can choose the values of the parameters.        -   Process the data sources and interact with the server to add            subscriptions for each query identified by a data source.        -   Parse the component section, and instantiate a scripting            object for each component based on a “factory” of available            components. Once instantiated the object is provided the            parameters of the visualization as well as the XML fragment            that configures it.    -   At this point control returns to the client which coordinates        the work of the various components as well as the interactions        with the server. This work is accomplished by handling various        events that are raised by the underlying plugin infrastructure.        These events are of two types:        -   User interaction events that are found in standard            event-driven UIs. Examples include moving the mouse,            clicking on something, navigating to a specific tab, etc.        -   Data update events that are raised when new data arrives            from the server for an active subscription.    -   For either event, the client identifies the component scripting        object that is responsible for handling it, and then dispatches        the event to that object. The scripting object is responsible        for visually rendering the data communicated to it using the        underlying infrastructure provided by the plugin, such as        charting.

Advanced Solution Device

In the advanced version of the solution, the following may beaccomplished:

Managing History

A chart that needs history (e.g., a line chart) is configured with anextra parameter describing the amount of history in terms of the numberof windows that the chart may preserve. In this context an issue to besolved is the initial set of history data to be bound to the chart whenit is first activated. This is accomplished by having the client requesta backfill of data from the server while activating a chart.

Restricting Data

A visualization widget may render only a subset of the actual data thatis produced by a CQ. This may be for two reasons:

-   -   1. In order to let a user control how the widget can manipulate        a given data set in different ways, possibly controlling how        much data is actually rendered in any given configuration. One        common use for such functionality is in order to “focus” on a        subset of the data that the user has expressed interest in.    -   2. A given CQ might produce a common set of results that might        apply to multiple different visualization widgets. For instance,        we might have a CQ that produces summary market information for        multiple currency pairs, and the CQ serves as a data source for        a visualization widget that is instantiated by different clients        and with a different value for a parameter that restricts the        data by currency pair. In such a situation it is useful to        annotate a subscription on a data source with a restriction that        is based on a specific parameter value.

While both of the situations above are similar they are used toaccomplish different goals. In the former instance the goal is to fetcha single, possibly large, set of data from the server to the client andthen winnow it in several different ways without going back to theserver each time. In the latter instance the goal is to repeatedly fetcha limited set of data and avoid including any extraneous data that isknown to be unnecessary at the client.

In either instance the idea is to annotate a data source specificationwith a “filter” in the XML that defines a visualization widget. There isan additional setting for the filter annotation which has two possiblechoices:

-   -   1. Run “on client”: This is suitable for the first situation        described above where the client restricts the data being        rendered but does not affect the actual data fetched from the        server.    -   2. Run “on server”: This is suitable for the second situation        described above, and requires an extra communication step to the        server application which in turn applies the filter for each        data tuple produced by the CQ while processing the subscription        associated with a given data source.

Extensibility

Extensibility can be accomplished in two ways:

-   -   1. Where a user adds a custom component (e.g., a composite        bar-pie chart) by adding an associated user-provided scripting        object and registering the association of the object to a        specific tag in the visualization XML so that the factory can        instantiate it.    -   2. Where a user modifies the functionality of an existing        component (e.g., to add annotations to a chart) by implementing        a hook that gets invoked by the component factory when        activating a component. The hook gets specified in the XML        document that describes the visualization widget.

1. A method for managing a data stream, comprising: generating aniterator-model subplan from an iterator-model plan; assigning acontinuous-query operator to the iterator-model subplan; and assigningan adapter to the iterator-model subplan.
 2. The method of claim 1,wherein the adapter comprises a new leaf node to the iterator-modelsubplan.
 3. The method of claim 1, further comprising: receiving acontinuous-query tuple; and providing the continuous-query tuple to aparent of the continuous-query operator.
 4. A method for managing a datastream, comprising: providing a data tuple to a stream-to-relationoperator; assigning a control tuple to the data tuple.
 5. The method ofclaim 4, wherein the control tuple includes a timestamp.
 6. The methodof claim 4, wherein assigning the control tuple to the data tupleincludes identifying the trigger of a window edge.
 7. The method ofclaim 6, further comprising: routing the data tuple to a downstreamoperator; and routing the control tuple to the downstream operator afterrouting the data tuple.
 8. A method for providing data for display basedon a continuous query, comprising: receiving a result of a continuousquery to a data stream; receiving a request based on a parameter; andproviding for display the result of the continuous query based on therequest.